package net.zoneland.knowledge.dao;

import java.util.List;

import net.zoneland.knowledge.model.SanKeReverse;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import net.zoneland.knowledge.controller.bean.UomUserOrgQueryVo;
import net.zoneland.knowledge.model.PartyOrg;
import net.zoneland.knowledge.model.PartyOrgVoForSelect;

/**
 * 党组织数据接口.
 * @author zmq
 * @Date 2022/8/26 15:24
 */
@Mapper
public interface PartyOrgMapper {
    /**
     * 查询党组织.
     * @param orgName 组织名称
     * @return 党组织集合
     */
    @Select({
        "select a.ORG_UUID, a.ORG_NAME, a.TEL, a.ORG_CHANGE_TIME, a.ADDRESS, a.CODE, a.ORG_TYPE, ",
        "a.WORK_ORGANIZATION, a.WORK_ORGANIZATION_CODE, a.WORK_ORGANIZATION_TYPE, a.ORG_CONTACTS, a.DANG_QUN_WORKDEPARTMENT, ",
        "a.DANG_JIAN_DEPARTMENT, a.DBZR, a.DBFZR, a.DWSJ,  a.ORG_LEADER_PAST, a.IMPORT_TIME, ",
        "a.ORG_SHORT_NAME, a.ORG_SJ, a.ORG_DW, a.SHORT_NAME2, a.SJ_MOBILE, a.SJ_ID, a.COMPANY_DN, b.ENABLED FAVORITE_STATUS",
        "FROM AITK_PARTY_ORG a",
        "left join AITK_FAVORITE b on a.ORG_UUID = b.ES_QUESTION_ID and b.CREATOR_UID = #{userId,jdbcType=VARCHAR} ",
        "WHERE ORG_NAME like #{orgName,jdbcType=VARCHAR} ",
    })
    @Results(id = "orgResultMap", value = {
        @Result(column="ORG_UUID", property="orgUUID", jdbcType= JdbcType.VARCHAR, id=true),
        @Result(column="ORG_NAME", property="orgName", jdbcType=JdbcType.VARCHAR),
        @Result(column="TEL", property="tel", jdbcType=JdbcType.VARCHAR),
        @Result(column="ORG_CHANGE_TIME", property="orgChangeTime", jdbcType=JdbcType.VARCHAR),
        @Result(column="ADDRESS", property="address", jdbcType=JdbcType.VARCHAR),
        @Result(column="CODE", property="code", jdbcType=JdbcType.VARCHAR),
        @Result(column="ORG_TYPE", property="orgType", jdbcType=JdbcType.VARCHAR),
        @Result(column="WORK_ORGANIZATION", property="workOrganization", jdbcType=JdbcType.VARCHAR),
        @Result(column="WORK_ORGANIZATION_CODE", property="workOrganizationCode", jdbcType=JdbcType.VARCHAR),
        @Result(column="WORK_ORGANIZATION_TYPE", property="workOrganizationType", jdbcType=JdbcType.VARCHAR),
        @Result(column="ORG_CONTACTS", property="orgContacts", jdbcType=JdbcType.VARCHAR),
        @Result(column="DANG_QUN_WORKDEPARTMENT", property="dangQunWorkDepartment", jdbcType=JdbcType.VARCHAR),
        @Result(column="DANG_JIAN_DEPARTMENT", property="dangJianDepartment", jdbcType=JdbcType.VARCHAR),
        @Result(column="DBZR", property="dbzr", jdbcType=JdbcType.VARCHAR),
        @Result(column="DBFZR", property="dbfzr", jdbcType=JdbcType.VARCHAR),
        @Result(column="DWSJ", property="dwsj", jdbcType=JdbcType.VARCHAR),
//        @Result(column="ORG_LEADER", property="orgLeader", jdbcType=JdbcType.VARCHAR),
        @Result(column="ORG_LEADER_PAST", property="orgLeaderPast", jdbcType=JdbcType.VARCHAR),
        @Result(column="IMPORT_TIME", property="importTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="ORG_SHORT_NAME", property="orgShortName", jdbcType=JdbcType.VARCHAR),
        @Result(column="ORG_SJ", property="orgSj", jdbcType=JdbcType.VARCHAR),
        @Result(column="ORG_DW", property="orgDw", jdbcType=JdbcType.VARCHAR),
        @Result(column="ORG_SHOT_NAME2", property="shortName2", jdbcType=JdbcType.VARCHAR),
        @Result(column="SJ_MOBILE", property="sjMobile", jdbcType=JdbcType.VARCHAR),
        @Result(column="SJ_ID", property="sjId", jdbcType=JdbcType.VARCHAR),
        @Result(column="PARENT_ORG_UUID", property="parentOrgUuid", jdbcType=JdbcType.VARCHAR),
        @Result(column="COMPANY_DN", property="companyDn", jdbcType=JdbcType.VARCHAR),
        @Result(column = "FAVORITE_STATUS", property = "favoriteStatus", jdbcType = JdbcType.BOOLEAN)
    })
    List<PartyOrg> search(@Param("orgName") String orgName, @Param("userId") String userId);

    /**
     * 查询党组织.
     * @param beginTime 时间
     * @return 党组织集合
     */
    @Select({
            "select ORG_UUID, ORG_NAME, TEL, ORG_CHANGE_TIME, ADDRESS, CODE, ORG_TYPE, ",
            "WORK_ORGANIZATION, WORK_ORGANIZATION_CODE, WORK_ORGANIZATION_TYPE, ORG_CONTACTS, DANG_QUN_WORKDEPARTMENT, ",
            "DANG_JIAN_DEPARTMENT, DBZR, DBFZR, DWSJ, ORG_LEADER, ORG_LEADER_PAST, IMPORT_TIME, ",
            "ORG_SHORT_NAME, ORG_SJ, ORG_DW, SHORT_NAME2, SJ_MOBILE, SJ_ID ",
            "FROM AITK_PARTY_ORG WHERE IMPORT_TIME>=to_date(#{beginTime},'yyyy-mm-dd HH:mi:ss')",
    })
    @ResultMap(value = {"orgResultMap"})
    List<PartyOrg> getOrgByTime(String beginTime);

    /**
     * 根据组织id查询组织.
     * @param orgUUID 组织id
     * @return 党组织集合
     */
    @Select({
        "select ORG_UUID, ORG_NAME, TEL, ORG_CHANGE_TIME, ADDRESS, CODE, ORG_TYPE, ",
        "WORK_ORGANIZATION, WORK_ORGANIZATION_CODE, WORK_ORGANIZATION_TYPE, ORG_CONTACTS, DANG_QUN_WORKDEPARTMENT, ",
        "DANG_JIAN_DEPARTMENT, DBZR, DBFZR, DWSJ, ORG_LEADER, ORG_LEADER_PAST, IMPORT_TIME, ",
        "ORG_SHORT_NAME, ORG_SJ, ORG_DW, SHORT_NAME2, SJ_MOBILE, SJ_ID, PARENT_ORG_UUID ",
        "FROM AITK_PARTY_ORG WHERE ORG_UUID = #{orgUUID, jdbcType=VARCHAR}",
    })
    @ResultMap(value = {"orgResultMap"})
    PartyOrg getOrgByUuid(String orgUUID);

    @Select({
        "<script>",
        "select ORG_UUID,ORG_SHORT_NAME, PARENT_ORG_UUID",
        "FROM AITK_PARTY_ORG ",
        "WHERE 1 = 1 ",
        "<if test=\"orgUuid != null and orgUuid != ''\"> AND ORG_UUID=#{orgUuid,jdbcType=VARCHAR} </if> ",
        "<if test=\"parentOrgUuid != null and parentOrgUuid != ''\"> AND PARENT_ORG_UUID=#{parentOrgUuid,jdbcType=VARCHAR} </if> ",
        "</script>"
    })
    @Results({
        @Result(column="ORG_UUID", property="orgUUID", jdbcType= JdbcType.VARCHAR, id=true),
        @Result(column="ORG_SHORT_NAME", property="orgShortName", jdbcType=JdbcType.VARCHAR),
        @Result(column="PARENT_ORG_UUID", property="parentOrgUuid", jdbcType=JdbcType.VARCHAR)
    })
    List<PartyOrgVoForSelect> searchOrgByConditionForTree(UomUserOrgQueryVo condition);

    /**
     * 查询党组织.
     * @return 党组织集合
     */
    @Select({
            "<script>",
            "select companyName, orgName1, orgName2, groupName, ORG_UUID, case ENABLED when 1 then '有效' else '无效' end as orgStatue from (",
            "   select a.ORG_SHORT_NAME companyName, b.ORG_SHORT_NAME orgName1, null orgName2 ,null groupName, b.ORG_UUID ORG_UUID, b.ENABLED",
            "   FROM AITK_PARTY_ORG a, AITK_PARTY_ORG b ",
            "   WHERE a.ORG_UUID = b.PARENT_ORG_UUID and a.ORG_TYPE = '党委' and b.ORG_TYPE = '党总支'",
            "   <if test=\"companyDn != null and companyDn != ''\"> and a.COMPANY_DN = #{companyDn, jdbcType=VARCHAR} </if> ",
            "   union all",
            "   select a.ORG_SHORT_NAME companyName, b.ORG_SHORT_NAME orgName1, b.ORG_SHORT_NAME orgName2 ,null groupName, b.ORG_UUID ORG_UUID, b.ENABLED",
            "   FROM AITK_PARTY_ORG a, AITK_PARTY_ORG b ",
            "   WHERE a.ORG_UUID = b.PARENT_ORG_UUID and a.ORG_TYPE = '党委' and b.ORG_TYPE = '党支部'",
            "   <if test=\"companyDn != null and companyDn != ''\"> and a.COMPANY_DN = #{companyDn, jdbcType=VARCHAR} </if> ",
            "   union all",
            "   select  c.ORG_SHORT_NAME companyName, a.ORG_SHORT_NAME orgName1, b.ORG_SHORT_NAME orgName2,null groupName, b.ORG_UUID ORG_UUID, b.ENABLED ",
            "   FROM AITK_PARTY_ORG a, AITK_PARTY_ORG b, AITK_PARTY_ORG c ",
            "   WHERE a.ORG_UUID = b.PARENT_ORG_UUID and a.PARENT_ORG_UUID = c.ORG_UUID and a.ORG_TYPE = '党总支'",
            "   <if test=\"companyDn != null and companyDn != ''\"> and a.COMPANY_DN = #{companyDn, jdbcType=VARCHAR} </if> ",
            "   union all",
            "   select companyName, orgName1, orgName2, d.ORG_GROUP_NAME groupName, d.ORG_GROUP_UUID ORG_UUID, d.ENABLED from",
            "       (select a.ORG_SHORT_NAME companyName, b.ORG_SHORT_NAME orgName1, b.ORG_SHORT_NAME orgName2 ,null groupName, b.ORG_UUID ORG_UUID, b.ENABLED",
            "       FROM AITK_PARTY_ORG a, AITK_PARTY_ORG b ",
            "       WHERE a.ORG_UUID = b.PARENT_ORG_UUID and a.ORG_TYPE = '党委'",
            "       <if test=\"companyDn != null and companyDn != ''\"> and a.COMPANY_DN = #{companyDn, jdbcType=VARCHAR} </if> ",
            "       union all",
            "       select c.ORG_SHORT_NAME companyName, a.ORG_SHORT_NAME orgName1, b.ORG_SHORT_NAME orgName2,null groupName, b.ORG_UUID ORG_UUID, b.ENABLED",
            "       FROM AITK_PARTY_ORG a, AITK_PARTY_ORG b, AITK_PARTY_ORG c ",
            "       WHERE a.ORG_UUID = b.PARENT_ORG_UUID and a.PARENT_ORG_UUID = c.ORG_UUID and a.ORG_TYPE = '党总支'",
            "       <if test=\"companyDn != null and companyDn != ''\"> and a.COMPANY_DN = #{companyDn, jdbcType=VARCHAR} </if> ",
            "   ) org, AITK_PARTY_GROUP d where d.ORG_PARENT_UUID = org.ORG_UUID",
            ") t ",
            "order by companyName asc, orgName1 desc, orgName2 desc, groupName desc",
            "</script>"
    })
    @Results({
            @Result(column="groupName", property="groupName", jdbcType= JdbcType.VARCHAR),
            @Result(column="companyName", property="companyName", jdbcType= JdbcType.VARCHAR),
            @Result(column="orgName1", property="orgName1", jdbcType=JdbcType.VARCHAR),
            @Result(column="orgName2", property="orgName2", jdbcType=JdbcType.VARCHAR),
            @Result(column="orgType", property="orgType", jdbcType=JdbcType.VARCHAR),
            @Result(column="ORG_UUID", property="orgUuid", jdbcType=JdbcType.VARCHAR),
            @Result(column="orgStatue", property="orgStatue", jdbcType=JdbcType.VARCHAR)
    })
    List<SanKeReverse> getOrg(@Param("companyDn") String companyDn);

    /**
     * 根据组织id集合查询组织.
     * @param orgUuids 组织id集合
     * @return 党组织集合
     */
    @Select({
            "<script>",
            "select ORG_UUID, ORG_NAME, TEL, ORG_CHANGE_TIME, ADDRESS, CODE, ORG_TYPE, ",
            "WORK_ORGANIZATION, WORK_ORGANIZATION_CODE, WORK_ORGANIZATION_TYPE, ORG_CONTACTS, DANG_QUN_WORKDEPARTMENT, ",
            "DANG_JIAN_DEPARTMENT, DBZR, DBFZR, DWSJ, ORG_LEADER, ORG_LEADER_PAST, IMPORT_TIME, ",
            "ORG_SHORT_NAME, ORG_SJ, ORG_DW, SHORT_NAME2, SJ_MOBILE, SJ_ID ",
            "FROM AITK_PARTY_ORG WHERE ORG_UUID in ",
            "<foreach collection='orgUuids' index='index' item='item' open='(' separator=',' close=')'>",
            "#{item}</foreach> ",
            "</script>"
    })
    @ResultMap(value = {"orgResultMap"})
    List<PartyOrg> getOrgByUuids(@Param("orgUuids") List<String> orgUuids);

    /**
     * 查询组织树.
     * @return 党组织集合
     */
    @Select({
            "SELECT DISTINCT COMPANY_NAME, ORG_SHORT_NAME ,ORG_UUID ",
            "from AITK_PARTY_ORG where COMPANY_NAME is not null and ENABLED = 1 and ORG_TYPE != '党委' ",
            "group BY COMPANY_NAME, ORG_SHORT_NAME, ORG_UUID ",
            "ORDER BY DECODE(COMPANY_NAME, '省公司', 1, '杭州分公司', 2, '宁波分公司', 3, '温州分公司', 4, '台州分公司', ",
            "5, '金华分公司', 6, '绍兴分公司', 7, '嘉兴分公司', 8, '湖州分公司', 9, '丽水分公司', 10, '舟山分公司', 11, ",
            "'衢州分公司', 12) , ORG_SHORT_NAME",
    })
    @Results({
            @Result(column="ORG_UUID", property="orgUUID", jdbcType=JdbcType.VARCHAR),
            @Result(column="ORG_SHORT_NAME", property="orgShortName", jdbcType=JdbcType.VARCHAR),
            @Result(column="COMPANY_NAME", property="companyName", jdbcType=JdbcType.VARCHAR),
    })
    List<PartyOrg> getOrgTree();

}
